package com.pe.pgn.clubpgn.dao.impl;

import java.util.Calendar;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.pe.pgn.clubpgn.common.CLPConstantes;
import com.pe.pgn.clubpgn.dao.CategoriaDao;
import com.pe.pgn.clubpgn.dao.hibernate.GenericDaoHibernate;
import com.pe.pgn.clubpgn.domain.ClpbCategoria;

@Repository("categoriaDao")
public class CategoriaDaoImpl extends GenericDaoHibernate<ClpbCategoria, Long> implements
		CategoriaDao {

	public CategoriaDaoImpl(){
		super(ClpbCategoria.class);
	}

	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> buscarCategorias(String deCategoria,
			String coCodigoCategoria, String stCategoria) {
		
		StringBuffer query = new StringBuffer();
		query.append("select id as id, ");
		query.append("		de_categoria as deCategoria, ");
		query.append("		co_codigo_categoria as coCodigoCategoria, ");
		query.append("		st_categoria as stCategoria");
		query.append("	from clpb_categoria ");
		query.append("	where 1 = 1	");
		
		if(StringUtils.isNotBlank(deCategoria)){
			query.append("		and upper(trim(de_categoria)) like '%").append(deCategoria.trim().toUpperCase()).append("%'");
		}
		
		if(StringUtils.isNotBlank(coCodigoCategoria)){
			query.append("		and upper(trim(co_codigo_categoria)) like '%").append(coCodigoCategoria.trim().toUpperCase()).append("%'");
		}
		
		if(StringUtils.isNotBlank(stCategoria) 
				&& !stCategoria.equalsIgnoreCase(CLPConstantes.CARACTER_GUION_NO_ESPACIOS)){
			query.append("		and st_categoria = '").append(stCategoria).append("' ");
		}
		System.out.println(query.toString());
		List<Map<String,Object>> list = findListOfMaps(query.toString(), null);
		return list;
	}


	public void eliminarCategoria(Long id) {
		
		remove(id);
		getHibernateTemplate().flush();		
	}


	public void guardarCategoria(ClpbCategoria categoria) {
		
		if(categoria.getId() == null){
			categoria.setCoUsuarioCreador(getUsuarioLogueado().getUsername());
			categoria.setDaFechaCreacion(Calendar.getInstance());
		}
		else{
			categoria.setCoUsuarioModificador(getUsuarioLogueado().getUsername());
			categoria.setDaFechaModificacion(Calendar.getInstance());
		}
		
		getHibernateTemplate().saveOrUpdate(categoria);
		getHibernateTemplate().flush();
	}


	public ClpbCategoria obtenerCategoria(Long id) {
		
		ClpbCategoria categoria = get(id);
		return categoria;
	}


	@SuppressWarnings("unchecked")
	public List<ClpbCategoria> obtenerCategorias() {
		
		Criteria criteria = DetachedCriteria.forClass(ClpbCategoria.class)
				.getExecutableCriteria(getSessionFactory().getCurrentSession());

		criteria.setMaxResults(CLPConstantes.MAXIMO_RESULTADO_INICIAL);
		criteria.add(Restrictions.eq("stCategoria", Boolean.TRUE));
		criteria.addOrder(Order.desc("id"));
		
		List<ClpbCategoria> lista = criteria.list(); 
		return lista;
	}
	
	public boolean esCodigoCategoriaRepetido(ClpbCategoria categoria){
		
		StringBuffer query = new StringBuffer();
		query.append("select count(*) ");
		query.append("	from clpb_categoria ");
		query.append("	where upper(trim(co_codigo_categoria)) = '").append(categoria.getCoCodigoCategoria().trim().toUpperCase()).append("' ");
		
		if(categoria.getId() != null){
			query.append(" and id != ").append(categoria.getId());
		}		
		
		query.append("		and st_categoria = '").append(CLPConstantes.ST_VERDADERO).append("' ");
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		return false;			
	}
	
	public boolean esCategoriaConDependencias(Long id){
		
		StringBuffer query = new StringBuffer();
		query.append("select count(*) ");
		query.append("	from clpd_articulo  ");
		query.append("	where co_categoria = ").append(id);
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		return false;
	}

	@SuppressWarnings("unchecked")
	public List<ClpbCategoria> obtenerTodoCategorias() {
		
		Criteria criteria = DetachedCriteria.forClass(ClpbCategoria.class)
		.getExecutableCriteria(getSessionFactory().getCurrentSession());

		criteria.add(Restrictions.eq("stCategoria", Boolean.TRUE));
		criteria.addOrder(Order.asc("deCategoria"));
		
		List<ClpbCategoria> lista = criteria.list(); 
		return lista;
	}
}
